import os
import sys
import time
import warnings
from datetime import datetime

import numpy as np
import pandas as pd
from sqlalchemy import create_engine

warnings.filterwarnings("ignore")

db_selection = {
    "iPS_itsm": "mysql+pymysql://pshread:pshread2020@172.22.2.178:3306/itsm_service",
    "aliyun_data": "mysql+pymysql://uaes_hzg:uaes@1234@172.22.2.178:3306/data",
}


def build_connection(db):
    # n = 0
    # while n < 30:
    try:
        engine = create_engine(db_selection[db], pool_size=5)
        return engine.connect()
    except Exception as e:
        logger(e)
        #     time.sleep(2)
        #     n += 1


def logger(message, ip=None, start=None):
    time_cost = 0 if start is None else int((time.time() - start) * 1000)
    file = sys._getframe(1).f_code.co_filename.split(os.sep)[-1]
    func = sys._getframe(1).f_code.co_name
    print(f"""('{datetime.now()}','{ip}','{file}','{func}','{message}',{time_cost})""")


def get_project_network():
    SQL = f"""
        select distinct
concat('p',CAST(project_id AS CHAR)) project_id
, concat('u',CAST(project_manager_id AS CHAR))  project_manager_id
, project_name 
, concat('e',CAST(id AS CHAR)) event_id 
, concat('[',event_num,'] ',title) event_title
, concat('u',CAST(a.employee_id AS CHAR))  employee_id
, concat(TRIM(TRAILING  ')' from SUBSTRING_INDEX(SUBSTRING_INDEX(he.employee_name, '(', 2), '(',-1)),
        '\n', SUBSTRING_INDEX(he.employee_name, '(', 1)) employee_name
, concat('s',CAST(hp.organization_id AS CHAR))  section_id
, ho.organization_code section_name
from (
# 触发立项的问题概览
select
pp.project_id
, pp.project_manager_id
, pp.project_name 
, eo.id
, eo.event_num 
, eo.title 
, ip.employee_id
from project_service.pm_project pp 
inner join itsm_service.event_order eo
on pp.project_code like '1515PROJECT%%'
and pp.is_deleted = 'N'
and pp.source_event_id = eo.id
left join itsm_service.itsm_participant ip 
on eo.iam_organization_id = ip.iam_organization_id 
and ip.category = 'EVENT'
and eo.id = ip.category_source_id 
and ip.employee_id not in (6, 29533)
union 
# 项目参与人
select
pp.project_id
, pp.project_manager_id
, pp.project_name 
, eo.id
, eo.event_num 
, eo.title 
, cgp.user_id employee_id
from project_service.pm_project pp 
inner join itsm_service.event_order eo
on pp.project_code like '1515PROJECT%%'
and pp.is_deleted = 'N'
and pp.source_event_id = eo.id
inner join itsm_service.ca_group cg 
on pp.project_id = cg.project_id 
and cg.iam_organization_id = 15
and cg.is_deleted = 'N'
inner join itsm_service.ca_group_person cgp 
on cgp.iam_organization_id = cg.iam_organization_id 
and cgp.group_id = cg.group_id 
and cgp.is_enabled = 'Y'
and cgp.is_deleted = 'N'
and cgp.user_id not in (6, 29533)
union
# 项目OPL参与人
select 
pp.project_id
, pp.project_manager_id
, pp.project_name 
, eo.id
, eo.event_num 
, eo.title 
, ip.employee_id
from itsm_service.event_order eo 
inner join project_service.pm_project pp 
on eo.iam_organization_id = 15
and eo.is_deleted = 'N'
and pp.project_id = eo.project_id 
and pp.is_deleted = 'N'
and pp.project_code like '1515PROJECT%%'
left join itsm_service.itsm_participant ip 
on eo.iam_organization_id = ip.iam_organization_id 
and ip.category = 'EVENT'
and eo.id = ip.category_source_id 
and ip.employee_id not in (6, 29533)
) a
inner join fnd_service.hr_employee he 
on a.employee_id = he.employee_id
inner join fnd_service.hr_employee_position hep 
on hep.employee_id = he.employee_id 
and hep.is_enabled = 'Y'
inner join fnd_service.hr_position hp 
on hep.position_id = hp.position_id 
inner join fnd_service.hr_organization ho 
on hp.organization_id = ho.organization_id 
    """
    try:
        conn = build_connection("iPS_itsm")
        network = pd.read_sql(SQL, conn)
    except Exception as e:
        logger(e)
        network = pd.read_csv("network.csv")
        return network
    else:
        network.to_csv("network.csv")
        return network


def abstract_node():
    pass


def gen_node_edge(network):
    project = network.groupby(["project_id", "project_name"])["project_id"].count().rename("weight").reset_index()
    project = project.sort_values(by="project_id", ascending=True).reset_index(drop=True)
    project['angle'] = project.index.map(lambda x: 2 * np.pi * x / len(project))
    project_r = 200
    project['x'] = project['angle'].map(lambda x: project_r * np.sin(x))
    project['y'] = project['angle'].map(lambda x: project_r * np.cos(x))
    project['node'] = project.apply(
        lambda row: {
            'classes': "project",
            'data': {
                'id': row['project_id'],
                'label': row['project_name'],
                'weight': row['weight'],
                # 'level': 1,
            },
            'position': {
                'x': row['x'],
                'y': row['y'],
            }
        }, axis=1
    )
    project_nodes = list(project['node'])

    person = network.groupby(["section_id", "section_name", "employee_id", "employee_name"])["employee_id"].count().\
        rename("weight").reset_index()
    person = person.sort_values(by=["section_name", "employee_name"], ascending=True).reset_index(drop=True)
    person['angle'] = person.index.map(lambda x: 2 * np.pi * x / len(person))
    person_r = 500
    person['x'] = person['angle'].map(lambda x: person_r * np.sin(x))
    person['y'] = person['angle'].map(lambda x: person_r * np.cos(x))
    person['node'] = person.apply(
        lambda row: {
            'classes': "person",
            'data': {
                'id': row['employee_id'],
                'label': row['employee_name'],
                'weight': row['weight'],
                # 'angle': str(row['angle'] / np.pi * 180) + 'deg',
                'angle': np.pi-row['angle'],
                # 'level': 2,
            },
            'position': {
                'x': row['x'],
                'y': row['y'],
            }
        }, axis=1
    )
    person_nodes = list(person['node'])

    section = person[["section_id", "section_name", 'angle', 'x', 'y']].\
        drop_duplicates(subset=["section_id", "section_name"], keep='last').sort_index()
    section['node'] = section.apply(
        lambda row: {
            'classes': "section",
            'data': {
                'id': row['section_id'],
                'label': row['section_name'],
                'angle': np.pi-row['angle'],
                # 'level': 2,
            },
            'position': {
                'x': row['x']*1.06,
                'y': row['y']*1.06,
            },
            'selectable':False,
        }, axis=1
    )
    section_nodes = list(section['node'])

    project_person = network.groupby(["project_id", "project_manager_id", "employee_id"])["event_id"].count().rename(
        "weight").reset_index()
    project_person['edge'] = project_person.apply(
        lambda row: {
            'classes': "participant manager" if row['employee_id'] == row['project_manager_id'] else "participant",
            'data': {
                'source': row['project_id'],
                'target': row['employee_id'],
                'label': row['weight'],
                # 'manager': True if row['employee_id'] == row['project_manager_id'] else False
            }
        }, axis=1
    )
    project_person = list(project_person['edge'])

    # section_person = person


    elements = project_nodes + person_nodes + section_nodes + project_person
    # print(elements)
    return elements


network = get_project_network()
elements = gen_node_edge(network)
